package GameHallDBController;

import java.sql.*;
import GameHallDeskModel.*;
import java.util.*;

public class HallDeskDB {	
	
	public static HallDeskDB desk=new HallDeskDB();
	private static Connection con=null;
    private static PreparedStatement stmt=null;        
    private static String Driver="com.mysql.jdbc.Driver";
    private static String url="jdbc:mysql://localhost:3306/GameUser";
    private static String gameuser="root";
    private static String password="586088";
    private static String sql;
    
    public HallDeskDB(){}
    
    public void HallDeskInit(){    		
    		
    		try{ 

                Class.forName(Driver); 

            } 

            catch(Exception e){ 

                System.out.println(e.toString()); 

            } 

            try{           
     
                con=DriverManager.getConnection(url,gameuser,password); 
                
                sql="insert into hallstate (Room ,Desk ,Players ,PlayerA ,PlayerB ) values (?,?,?,?,?)";             
                
                stmt=con.prepareStatement(sql);  
                
                for(int i=1;i<3;i++){
                	
                	for(int j=1;j<13;j++){
                		
                		stmt.setInt(1, i);
                		stmt.setInt(2, j);
                		stmt.setInt(3, 0);
                		stmt.setInt(4, 0);
                		stmt.setInt(5, 0);
                		
                		stmt.executeUpdate();
                	}
                }               

                if(!con.isClosed()) 

                    System.out.println("done"); 

                con.close();                

            } 
            catch(SQLException ee){             
               System.out.println(ee.toString());
            } 
            
    }
    
    public void RestartGameExchangeTurn(int room, int desk){
    
        HallDeskModel deskinfo=getDeskInfo(room, desk);
    	
    	deskinfo.exchangePlayers();    	
    	
    	try{ 

            Class.forName(Driver); 

        } 

        catch(Exception e){ 

            System.out.println(e.toString()); 

        } 

        try{           
 
            con=DriverManager.getConnection(url,gameuser,password); 
            
            sql="update hallstate set Players=? ,PlayerA=? ,PlayerB=? where Room=? and Desk=?";             
            
            stmt=con.prepareStatement(sql);  
            
            stmt.setInt(1, deskinfo.getPlayers());
            stmt.setInt(2, deskinfo.getPlayerA());
            stmt.setInt(3, deskinfo.getPlayerB());
            stmt.setInt(4, deskinfo.getRoom());
            stmt.setInt(5, deskinfo.getDesk());         
                  
            stmt.executeUpdate();                  
   
            if(!con.isClosed()) 

                System.out.println("done"); 

            con.close(); 

        } 
        catch(SQLException ee){             
           System.out.println(ee.toString());
        }
    }
    
    public void QuitGame(int id, int room, int desk){
    	
    	HallDeskModel deskinfo=getDeskInfo(room, desk);
    	
    	if(id==deskinfo.getPlayerA()){
    		
    		deskinfo.eliminatePlayer(id, 1);
    		
    	}else if(id==deskinfo.getPlayerB()){   
    		
    		deskinfo.eliminatePlayer(id, 2);
    	}
    	
    	
    	try{ 

            Class.forName(Driver); 

        } 

        catch(Exception e){ 

            System.out.println(e.toString()); 

        } 

        try{           
 
            con=DriverManager.getConnection(url,gameuser,password); 
            
            sql="update hallstate set Players=? ,PlayerA=? ,PlayerB=? where Room=? and Desk=?";             
            
            stmt=con.prepareStatement(sql);  
            
            stmt.setInt(1, deskinfo.getPlayers());
            stmt.setInt(2, deskinfo.getPlayerA());
            stmt.setInt(3, deskinfo.getPlayerB());
            stmt.setInt(4, deskinfo.getRoom());
            stmt.setInt(5, deskinfo.getDesk());         
                  
            stmt.executeUpdate();                  
   
            if(!con.isClosed()) 

                System.out.println("done"); 

            con.close(); 

        } 
        catch(SQLException ee){             
           System.out.println(ee.toString());
        }
    }
    
    public void PartakeGame(int id, int room, int desk){
    	
    	HallDeskModel deskinfo=getDeskInfo(room,desk);
    	
    	deskinfo.addPlayer(id);
    	
    	try{ 

            Class.forName(Driver); 

        } 

        catch(Exception e){ 

            System.out.println(e.toString()); 

        } 

        try{           
 
            con=DriverManager.getConnection(url,gameuser,password); 
            
            sql="update hallstate set Players=? ,PlayerA=? ,PlayerB=? where Room=? and Desk=?";             
            
            stmt=con.prepareStatement(sql);  
            
            stmt.setInt(1, deskinfo.getPlayers());
            stmt.setInt(2, deskinfo.getPlayerA());
            stmt.setInt(3, deskinfo.getPlayerB());
            stmt.setInt(4, deskinfo.getRoom());
            stmt.setInt(5, deskinfo.getDesk());         
                  
            stmt.executeUpdate();          

            if(!con.isClosed()) 

                System.out.println("done"); 

            con.close(); 

        } 
        catch(SQLException ee){            
            
           System.out.println(ee.toString());
        }
    }
    
    public HallDeskModel getDeskInfo(int room, int desk){
    	
    	ResultSet rs=null; 
    	HallDeskModel deskinfo=HallDeskModel.getInstance();
    	
    	try{ 

            Class.forName(Driver); 

        } 

        catch(Exception e){ 

            System.out.println(e.toString()); 

        } 

        try{           
 
            con=DriverManager.getConnection(url,gameuser,password); 
            
            sql="select * from hallstate where Room=? and Desk=?";             
            
            stmt=con.prepareStatement(sql);   
            
            stmt.setInt(1, room);
            stmt.setInt(2, desk);
                  
            rs=stmt.executeQuery();  
            
            if(rs.next()){
            	
            	deskinfo.setPlayerA(rs.getInt("PlayerA"));
            	deskinfo.setPlayerB(rs.getInt("PlayerB"));
            	deskinfo.setPlayers(rs.getInt("Players"));
            	deskinfo.setRoom(room);
            	deskinfo.setDesk(desk);
            }

            if(!con.isClosed()) 

                System.out.println("done"); 

            con.close(); 
            
            return deskinfo;

        } 
        catch(SQLException ee){ 
           System.out.println(ee.toString());
        }
        
        return null;
    	
    }
    
    public ArrayList<Integer> getFreeDeskList(int room){
    	
    	 ResultSet rs=null; 
    	 ArrayList<Integer> desklist=new ArrayList<Integer>();
         
         try{ 

             Class.forName(Driver); 

         } 

         catch(Exception e){ 

             System.out.println(e.toString()); 

         } 

         try{           
  
             con=DriverManager.getConnection(url,gameuser,password); 
             
             sql="select Desk from hallstate where Players=0";             
             
             stmt=con.prepareStatement(sql);              
                   
             rs=stmt.executeQuery(); 
             
             while(rs.next()){
            	 
            	 desklist.add(rs.getInt("Desk"));
             }
             
             sql="select Desk from hallstate where Players=1";             
             
             stmt=con.prepareStatement(sql);              
                   
             rs=stmt.executeQuery();
             
             while(rs.next()){
            	 
            	 desklist.add(-rs.getInt("Desk"));
             }
             
             rs.close();

             if(!con.isClosed()) 

                 System.out.println("done"); 

             con.close(); 
             
             return desklist;

         } 
         catch(SQLException ee){             
            System.out.println(ee.toString());
         }
         
         return null;
         
    }
    
    public static HallDeskDB getInstance(){
    	
    	return desk;
    }
    
    public void createTable(){
    	
    	Statement stmt=null;
        
        try{ 

           Class.forName(Driver); 

       } 

       catch(Exception e){ 

           System.out.println(e.toString()); 

       } 

       try{           

           con=DriverManager.getConnection(url,gameuser,password); 
           stmt=con.createStatement();
           sql="create table hallstate(Room int(10), Desk int(20), Players int(2), PlayerA int(100), PlayerB int(100))";            
           stmt.execute(sql);           

           if(!con.isClosed()) 

               System.out.println("done"); 

           con.close(); 

       } 
       catch(SQLException ee){ 
          System.out.println(ee.toString());
       }    
    } 
    
    public static void main(String[] args){
        HallDeskDB db=new HallDeskDB();
        db.HallDeskInit();
    }

}
